//
//  DateBaseHandler.m
//  HappyTrip
//
//  Created by dlios on 15-4-3.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DateBaseHandler.h"

@implementation DateBaseHandler

+ (DateBaseHandler *)shareInstance
{
    // 单例方法的实现
    static DateBaseHandler *dbHander = nil;
    // 但静态指针为空的时候创建一个单例对象
    if (dbHander == nil) {
        dbHander = [[DateBaseHandler alloc] init];
    }
    return dbHander;
}

- (void)judgeReselt:(int)result type:(NSString *)type
{
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", type);
    }else{
        NSLog(@"%@失败, 失败编号: %d", type, result);
    }
}

- (void)openDB
{
    // 打开数据库
    //拼接一个数据库路径
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"noteCollect.db"];
    // 参数1: 要打开的数据库的路径
    //参数2: 数据库文件的指针地址
    NSLog(@"0000000000 %@", dbPath);
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    NSLog(@"打开数据库结果: %d", result);
    [self judgeReselt:result type:@"打开数据库"];
}

- (void)creataTable
{
    // 创建一个noteCollect表
    NSString *sqlStr = @"create table noteCollect (name text, idNumber text primary key)";
    //参数1: 数据库指针
    //参数2: 要执行的sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeReselt:result type:@"创建表"];
}
- (void)deleteNoteCollecrt:(NoteCollect *)note
{
    NSString *selStr = [NSString stringWithFormat:@"delete from noteCollect where idNumber = '%@'", note.idNumber];
    int result = sqlite3_exec(dbPoint, [selStr UTF8String], NULL, NULL, NULL);
    [self judgeReselt:result type:@"删除noteCollect信息"];
}

- (void)inserNoteCollect:(NoteCollect *)note
{
    NSString *insStr = [NSString stringWithFormat:@"insert into noteCollect (name, idNumber) values ('%@', '%@')", note.name, note.idNumber];
    int result = sqlite3_exec(dbPoint, [insStr UTF8String], NULL, NULL, NULL);
    [self judgeReselt:result type:@"插入noteCollect信息"];
}

- (NSMutableArray *)selectAll
{
    NSString *sqlStr = @"select * from noteCollect";
    //数据库状态指针, 数据库执行sql语句的所有结果都保存在这个指针中
    sqlite3_stmt *stmt = nil;
    // 执行sql语句, 把sql语句的执行结果保存在stmt中
    // 参数1: 数据库指针
    // 参数2: sql语句
    // 参数3: 限制sql语句的长度
    // 参数4: stmt的指针地址
    int result = sqlite3_prepare(dbPoint, [sqlStr UTF8String], -1, &stmt, NULL);
    //创建一个空数组, 用来装noteCollect书数据
    NSMutableArray *noteCollectArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 遍历结果中的所有数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 每一条都对应一个noteCollect对象
            NoteCollect *note = [[NoteCollect alloc] init];
            // 按照列的顺序读取
            // 参数1: 状态指针
            // 参数2: 从第几列中取值(从0开始计数)
            const unsigned char *nameChar = sqlite3_column_text(stmt, 0);
            NSString *name = [NSString stringWithUTF8String:(const char *)nameChar];
            note.name = name;
            const unsigned char *idNumberStr = sqlite3_column_text(stmt, 1);
            NSString *idNumber = [NSString stringWithUTF8String:(const char *)idNumberStr];
            note.idNumber = idNumber;

            
            NSLog(@"*********** %@", idNumber);

            [noteCollectArr addObject:note];
            [note release];
        }
    }
    // 回收状态指针, 将sql的改变结果保存到数据库
    sqlite3_finalize(stmt);
    return noteCollectArr;
    
}









@end
